package com.yss.sofa.report.engine.core;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.map.ObjectMapper;

import com.yss.sofa.report.engine.Const;
import com.yss.sofa.report.engine.Context;
import com.yss.sofa.report.engine.DataSet;
import com.yss.sofa.report.engine.DataSetData;
import com.yss.sofa.report.engine.Parameter;
import com.yss.sofa.report.engine.Report;
import com.yss.sofa.report.engine.Template;
import com.yss.sofa.report.engine.cell.FloatingCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.HeterogeneousSqlDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.NoneDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.OsgiDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.ScriptDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.SqlDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.StaticDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.dataset.VirtualDataSetCell;
import com.yss.sofa.report.engine.cell.invisible.param.ParameterCell;
import com.yss.sofa.report.engine.cell.meta.MetaCell;
import com.yss.sofa.report.engine.cell.page.PageCell;
import com.yss.sofa.report.engine.cell.page.header.ColCell;
import com.yss.sofa.report.engine.cell.page.header.RowCell;
import com.yss.sofa.report.engine.cell.visible.SimpleCell;
import com.yss.sofa.report.engine.cell.visible.VisibleCell;
import com.yss.sofa.report.engine.cell.visible.extendable.ChartCell;
import com.yss.sofa.report.engine.cell.visible.extendable.DataCell;
import com.yss.sofa.report.engine.cell.visible.extendable.ExpressionCell;
import com.yss.sofa.report.engine.cell.visible.extendable.FieldCell;
import com.yss.sofa.report.engine.cell.visible.extendable.StaticCell;
import com.yss.sofa.report.engine.cell.visible.form.FormCell;
import com.yss.sofa.report.engine.cell.visible.result.ChartResultCell;
import com.yss.sofa.report.engine.cell.visible.result.FloatingResultCell;
import com.yss.sofa.report.engine.cell.visible.result.ResultCell;
import com.yss.sofa.report.engine.core.Transmission.Coordinate;
import com.yss.sofa.report.engine.poi.SheetUtil2;
import com.yss.sofa.report.engine.poi.SheetUtils;
import com.yss.sofa.report.engine.util.ChartUtil;
import com.yss.sofa.report.engine.util.ImageUtil;
import com.yss.sofa.report.engine.util.NumberUtil;
import com.yss.sofa.report.engine.util.RenderUtil;
import com.yss.sofa.report.engine.util.ScriptUtil;

public final class Piston {
	private Piston() {
	}

	public static Template readMainMeta(Template template, Workbook excel, FormulaEvaluator evaluator) throws Exception {
		Sheet sheet = excel.getSheet(Const.MAIN_META);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.MAIN_META + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		Set<Cell> cells = new HashSet<Cell>();
		// meta
		{
			Row row = sheet.getRow(rowcount);
			Cell cell = row.getCell(colcount);
			String value = Transmission.getExcelCellRawValue(cell);
			if ("##".equalsIgnoreCase(value)) {
				value = "##{}";
			}
			try {
				MetaCell meta = (MetaCell) Transmission.convertToReportCell(Integer.MIN_VALUE, value);
				Transmission.initMetaSettings(sheet, meta, rowcount, colcount);
				template.setMainMeta(meta);
			} catch (Exception e) {
				throw new Exception("无法解析sheet[" + Const.MAIN_META + "].meta的值，缺少必要的属性或不是有效的#JSON格式", e);
			}
		}
		// cells
		{
			for (int r = 0; r < rowcount; r++) {
				Row row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					Cell cell = row.getCell(c);
					String value = Transmission.getExcelCellRawValue(cell);
					if (cells.contains(cell)) {
						continue;
					} else {
						cells.add(cell);
					}
					try {
						if (Transmission.isJSON(value)) {
							int type = Transmission.getReportCellType(value);
							switch (type) {
							case Const.REPORT_CELL_TYPE_PARAM: {
								ParameterCell pc = (ParameterCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(pc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + pc.getCode() + "]不能是关键字或以sys开头");
								}
								template.getMainParams().add(pc);
								break;
							}
							case Const.REPORT_CELL_TYPE_SQL_DATASET: {
								SqlDataSetCell dc = (SqlDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String sql = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getSql());
								dc.setSql(sql);
								template.getMainDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_OSGI_DATASET: {
								OsgiDataSetCell dc = (OsgiDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								template.getMainDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_SCRIPT_DATASET: {
								ScriptDataSetCell dc = (ScriptDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String script = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getScript());
								dc.setScript(script);
								template.getMainDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_VIRTUAL_DATASET: {
								VirtualDataSetCell dc = (VirtualDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								template.getMainDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_STATIC_DATASET: {
								StaticDataSetCell dc = (StaticDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String begin = dc.getBegin();
								String end = dc.getEnd();
								DataSetData data = Transmission.excelRangeToData(sheet, evaluator, begin, end);
								dc.setData(data);
								template.getMainDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_HETEROGENEOUS_SQL_DATASET: {
								HeterogeneousSqlDataSetCell dc = (HeterogeneousSqlDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								Map<Integer, String> sqls = dc.getSqls();
								Set<Integer> dbtypes = sqls.keySet();
								for (int dbtype : dbtypes) {
									String sql = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getSqls().get(dbtype));
									sqls.put(dbtype, sql);
								}
								template.getMainDatasets().add(dc);
								break;
							}
							default:
								break;
							}
						}
					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.MAIN_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "]的值，缺少必要的属性或不是有效的#JSON格式", e);
					}
				}
			}
			template.getMainDatasets().add(new NoneDataSetCell());
		}
		return template;
	}

	public static Template readMain(Template template, Workbook excel, FormulaEvaluator evaluator) throws Exception {
		Sheet sheet = excel.getSheet(Const.MAIN);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.MAIN + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		List<VisibleCell> visiblecells = new LinkedList<VisibleCell>();
		Set<Cell> cells = new HashSet<Cell>();
		Map<CellRangeAddress, Boolean> cras = new HashMap<CellRangeAddress, Boolean>();
		int mercount = sheet.getNumMergedRegions();
		for (int i = 0; i < mercount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			cras.put(ca, false);
		}
		// page
		PageCell page;
		{
			// page
			Row row = sheet.getRow(rowcount);
			Cell cell = row.getCell(colcount);
			String value = Transmission.getExcelCellRawValue(cell);
			if ("##".equalsIgnoreCase(value)) {
				value = "##{}";
			}
			try {
				page = (PageCell) Transmission.convertToReportCell(Integer.MAX_VALUE, value);
				Transmission.initPageSettings(sheet, page, rowcount, colcount);
				if (StringUtils.isEmpty(page.getName())) {
					page.setName("<#if SYS_REPORT_NAME?length gt 31>${SYS_REPORT_NAME?substring(0,31)}<#else>${SYS_REPORT_NAME}</#if>");
				}
			} catch (Exception e) {
				throw new Exception("无法解析sheet[" + Const.MAIN + "].page的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
			}
		}
		// cells
		{
			for (int r = 0; r < rowcount; r++) {
				Row row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					Cell cell = row.getCell(c);
					if (cells.contains(cell)) {
						continue;
					} else {
						cells.add(cell);
					}
					String value = Transmission.getExcelCellRawValue(cell);
					try {
						int type = Transmission.getReportCellType(value);
						switch (type) {
						case Const.REPORT_CELL_TYPE_SIMPLE: {
							SimpleCell sc = (SimpleCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, sc, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_STATIC: {
							StaticCell sc = (StaticCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, sc, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_DATA: {
							DataCell dc = (DataCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, dc, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_EXP: {
							ExpressionCell ec = (ExpressionCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, ec, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_CHART: {
							ChartCell cc = (ChartCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, cc, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_FIELD: {
							FieldCell fc = (FieldCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, fc, r, c, cras);
							break;
						}
						default: {
							throw new Exception("sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].type[" + type + "]不可接受");
						}
						}
					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(r, c) + "]的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
					}
				}
			}
			template.addMain(Const.MAIN, page, visiblecells);
		}
		final int MAX_SUB_SHEET_COUNT = 7;
		int subSheetCount = 0;
		Iterator<Sheet> sheets = sheet.getWorkbook().sheetIterator();
		while (sheets.hasNext()) {
			Sheet mainSub = sheets.next();
			if (mainSub.getSheetName().startsWith("main.")) {
				String name = mainSub.getSheetName().substring(5);
				if (StringUtils.isEmpty(name)) {
					continue;
				}
				subSheetCount++;
				if (subSheetCount > MAX_SUB_SHEET_COUNT) {
					throw new Exception("sheet[main.*]的数量已超过最大值[" + MAX_SUB_SHEET_COUNT + "]");
				}
				visiblecells.clear();
				cells.clear();
				cras.clear();
				mercount = mainSub.getNumMergedRegions();
				for (int i = 0; i < mercount; i++) {
					CellRangeAddress ca = mainSub.getMergedRegion(i);
					cras.put(ca, false);
				}
				coordinate = Transmission.getTrueRowColCount(mainSub);
				rowcount = coordinate.row;
				colcount = coordinate.col;
				PageCell subPage;
				{
					// page
					Row row = mainSub.getRow(rowcount);
					Cell cell = row.getCell(colcount);
					String value = Transmission.getExcelCellRawValue(cell);
					if ("##".equalsIgnoreCase(value)) {
						value = "##{}";
					}
					try {
						subPage = (PageCell) Transmission.convertToReportCell(Integer.MAX_VALUE, value);
						Transmission.initPageSettings(mainSub, subPage, rowcount, colcount);
						if (StringUtils.isEmpty(subPage.getName())) {
							if (name.length() > 31) {
								subPage.setName(name.substring(0, 31));
							} else {
								subPage.setName(name);
							}
						}
					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + name + "].page的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
					}
				}
				for (int r = 0; r < rowcount; r++) {
					Row row = mainSub.getRow(r);
					for (int c = 0; c < colcount; c++) {
						Cell cell = row.getCell(c);
						String value = Transmission.getExcelCellRawValue(cell);
						try {
							int type = Transmission.getReportCellType(value);
							switch (type) {
							case Const.REPORT_CELL_TYPE_SIMPLE: {
								SimpleCell sc = (SimpleCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, sc, r, c, cras);
								break;
							}
							case Const.REPORT_CELL_TYPE_STATIC: {
								StaticCell sc = (StaticCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, sc, r, c, cras);
								break;
							}
							case Const.REPORT_CELL_TYPE_DATA: {
								DataCell dc = (DataCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, dc, r, c, cras);
								break;
							}
							case Const.REPORT_CELL_TYPE_EXP: {
								ExpressionCell ec = (ExpressionCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, ec, r, c, cras);
								break;
							}
							case Const.REPORT_CELL_TYPE_CHART: {
								ChartCell cc = (ChartCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, cc, r, c, cras);
								break;
							}
							case Const.REPORT_CELL_TYPE_FIELD: {
								FieldCell fc = (FieldCell) Transmission.convertToReportCell(type, value);
								Transmission.fillReportCell(visiblecells, fc, r, c, cras);
								break;
							}
							default: {
								throw new Exception("sheet[" + name + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].type[" + type + "]不可接受");
							}
							}
						} catch (Exception e) {
							throw new Exception("无法解析sheet[" + name + "].cell[" + NumberUtil.toExcelPoint(r, c) + "]的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
						}
					}
				}
				template.addMain(mainSub.getSheetName(), subPage, visiblecells);
			}
		}
		return template;
	}

	public static Template readFormMeta(Template template, Workbook excel, FormulaEvaluator evaluator) throws Exception {
		Sheet sheet = excel.getSheet(Const.FORM_META);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.FORM_META + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		Set<Cell> cells = new HashSet<Cell>();
		// meta
		{
			Row row = sheet.getRow(rowcount);
			Cell cell = row.getCell(colcount);
			String value = Transmission.getExcelCellRawValue(cell);
			if ("##".equalsIgnoreCase(value)) {
				value = "##{}";
			}
			try {
				MetaCell meta = (MetaCell) Transmission.convertToReportCell(Integer.MIN_VALUE, value);
				Transmission.initMetaSettings(sheet, meta, rowcount, colcount);
				template.setFormMeta(meta);
			} catch (Exception e) {
				throw new Exception("无法解析sheet[" + Const.FORM_META + "].meta的值，缺少必要的属性或不是有效的#JSON格式", e);
			}
		}
		// cells
		{
			for (int r = 0; r < rowcount; r++) {
				Row row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					Cell cell = row.getCell(c);
					String value = Transmission.getExcelCellRawValue(cell);
					if (cells.contains(cell)) {
						continue;
					} else {
						cells.add(cell);
					}
					try {
						if (Transmission.isJSON(value)) {
							int type = Transmission.getReportCellType(value);
							switch (type) {
							case Const.REPORT_CELL_TYPE_SQL_DATASET: {
								SqlDataSetCell dc = (SqlDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String sql = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getSql());
								dc.setSql(sql);
								template.getFormDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_OSGI_DATASET: {
								OsgiDataSetCell dc = (OsgiDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								template.getFormDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_SCRIPT_DATASET: {
								ScriptDataSetCell dc = (ScriptDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String script = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getScript());
								dc.setScript(script);
								template.getFormDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_STATIC_DATASET: {
								StaticDataSetCell dc = (StaticDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								String begin = dc.getBegin();
								String end = dc.getEnd();
								DataSetData data = Transmission.excelRangeToData(sheet, evaluator, begin, end);
								dc.setData(data);
								template.getFormDatasets().add(dc);
								break;
							}
							case Const.REPORT_CELL_TYPE_HETEROGENEOUS_SQL_DATASET: {
								HeterogeneousSqlDataSetCell dc = (HeterogeneousSqlDataSetCell) Transmission.convertToReportCell(type, value);
								if (Const.is_KEY_WORD(dc.getCode())) {
									throw new Exception("sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].code属性的值[" + dc.getCode() + "]不能是关键字或以sys开头");
								}
								Map<Integer, String> sqls = dc.getSqls();
								Set<Integer> dbtypes = sqls.keySet();
								for (int dbtype : dbtypes) {
									String sql = Transmission.getSpecifiedExcelCellRawValue(sheet, dc.getSqls().get(dbtype));
									sqls.put(dbtype, sql);
								}
								template.getFormDatasets().add(dc);
								break;
							}
							default:
								break;
							}
						}
					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.FORM_META + "].cell[" + NumberUtil.toExcelPoint(r, c) + "]的值，缺少必要的属性或不是有效的#JSON格式", e);
					}
				}
			}
			template.getFormDatasets().add(new NoneDataSetCell());
		}
		return template;
	}

	public static Template readForm(Template template, Workbook excel, FormulaEvaluator evaluator) throws Exception {
		Sheet sheet = excel.getSheet(Const.FORM);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.FORM + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		List<VisibleCell> visiblecells = new LinkedList<VisibleCell>();
		Set<Cell> cells = new HashSet<Cell>();
		Map<CellRangeAddress, Boolean> cras = new HashMap<CellRangeAddress, Boolean>();
		int mercount = sheet.getNumMergedRegions();
		for (int i = 0; i < mercount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			cras.put(ca, false);
		}
		// page
		{
			Row row = sheet.getRow(rowcount);
			Cell cell = row.getCell(colcount);
			String value = Transmission.getExcelCellRawValue(cell);
			if ("##".equalsIgnoreCase(value)) {
				value = "##{}";
			}
			try {
				PageCell page = (PageCell) Transmission.convertToReportCell(Integer.MAX_VALUE, value);
				Transmission.initPageSettings(sheet, page, rowcount, colcount);
				if (StringUtils.isEmpty(page.getName())) {
					page.setName("<#if SYS_REPORT_NAME?length gt 31>${SYS_REPORT_NAME?substring(0,31)}<#else>${SYS_REPORT_NAME}</#if>");
				}
				template.setFormPage(page);
			} catch (Exception e) {
				throw new Exception("无法解析sheet[" + Const.FORM + "].page的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
			}
		}
		// cells
		{
			for (int r = 0; r < rowcount; r++) {
				Row row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					Cell cell = row.getCell(c);
					if (cells.contains(cell)) {
						continue;
					} else {
						cells.add(cell);
					}
					String value = Transmission.getExcelCellRawValue(cell);
					try {
						int type = Transmission.getReportCellType(value);
						switch (type) {
						case Const.REPORT_CELL_TYPE_SIMPLE: {
							SimpleCell sc = (SimpleCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, sc, r, c, cras);
							break;
						}
						case Const.REPORT_CELL_TYPE_FORM: {
							FormCell fc = (FormCell) Transmission.convertToReportCell(type, value);
							Transmission.fillReportCell(visiblecells, fc, r, c, cras);
							break;
						}
						default: {
							throw new Exception("sheet[" + Const.FORM + "].cell[" + NumberUtil.toExcelPoint(r, c) + "].type[" + type + "]不可接受");
						}
						}
					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.FORM + "].cell[" + NumberUtil.toExcelPoint(r, c) + "]的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
					}
				}
			}
			template.getFormCells().addAll(visiblecells);
		}
		return template;
	}

	public static Template readStyle(Template template, Workbook excel, FormulaEvaluator evaluator) throws Exception {
		Sheet sheet = excel.getSheet(Const.STYLE);
		if (sheet == null) {
			template.setHasStyle(false);
		} else {
			template.setHasStyle(true);
			template.setHtml(Transmission.excelSheetToHTML(sheet));
		}
		return template;
	}

	public static Map<String, Serializable> convertAndFilterParams(Set<Parameter> params, Map<String, String> paramValues, boolean onlySys) throws Exception {
		Map<String, Serializable> result = new HashMap<String, Serializable>();
		boolean includeAllSysParams = true;
		for (String sp : Const.SYS_PARAMS) {
			includeAllSysParams = includeAllSysParams && paramValues.containsKey(sp) && StringUtils.isNotEmpty(paramValues.get(sp));
		}
		if (!includeAllSysParams) {
			throw new Exception("缺少系统隐含参数，即SYS_*参数");
		}
		if (!onlySys) {
			for (Parameter p : params) {
				try {
					int paramType = p.getParamType();
					String key = p.getCode();
					String __key__ = "__" + key + "__";
					String raw = p.getValue();
					String __raw__ = p.getValue();
					if (paramValues.containsKey(key)) {
						raw = paramValues.get(key);
					}
					if (paramValues.containsKey(__key__)) {
						__raw__ = paramValues.get(__key__);
					} else {
						__raw__ = raw;
					}
					Serializable value = null;
					switch (paramType) {
					case Const.PARAM_DATA_TYPE_STRING:
						value = raw;
						break;
					case Const.PARAM_DATA_TYPE_STRING_ARRAY:
						if (StringUtils.isNotEmpty(raw)) {
							value = raw.split(",");
						} else {
							value = new String[0];
						}
						break;
					case Const.PARAM_DATA_TYPE_INT:
						if (StringUtils.isNotEmpty(raw)) {
							value = Integer.valueOf(raw);
						}
						break;
					case Const.PARAM_DATA_TYPE_INT_ARRAY: {
						if (StringUtils.isNotEmpty(raw)) {
							String[] sa = raw.split(",");
							Integer[] ia = new Integer[sa.length];
							for (int i = 0; i < sa.length; i++) {
								ia[i] = Integer.valueOf(sa[i]);
							}
							value = ia;
						} else {
							value = new Integer[0];
						}
						break;
					}
					case Const.PARAM_DATA_TYPE_DOUBLE:
						if (StringUtils.isNotEmpty(raw)) {
							value = Double.valueOf(raw);
						}
						break;
					case Const.PARAM_DATA_TYPE_DOUBLE_ARRAY: {
						if (StringUtils.isNotEmpty(raw)) {
							String[] sa = raw.split(",");
							Double[] da = new Double[sa.length];
							for (int i = 0; i < sa.length; i++) {
								da[i] = Double.valueOf(sa[i]);
							}
							value = da;
						} else {
							value = new Double[0];
						}
						break;
					}
					case Const.PARAM_DATA_TYPE_DATE: {
						if (StringUtils.isNotEmpty(raw)) {
							SimpleDateFormat datefromat = new SimpleDateFormat("yyyy-MM-dd");
							value = datefromat.parse(raw);
						}
						break;
					}
					case Const.PARAM_DATA_TYPE_DATE_ARRAY: {
						if (StringUtils.isNotEmpty(raw)) {
							SimpleDateFormat datefromat = new SimpleDateFormat("yyyy-MM-dd");
							String[] sa = raw.split(",");
							Date[] da = new Date[sa.length];
							for (int i = 0; i < sa.length; i++) {
								da[i] = datefromat.parse(sa[i]);
							}
							value = da;
						} else {
							value = new Date[0];
						}
						break;
					}
					case Const.PARAM_DATA_TYPE_BOOLEAN:
						value = StringUtils.isEmpty(raw) ? false : Boolean.valueOf(raw);
						break;
					case Const.PARAM_DATA_TYPE_BOOLEAN_ARRAY: {
						if (StringUtils.isNotEmpty(raw)) {
							String[] sa = raw.split(",");
							Boolean[] ba = new Boolean[sa.length];
							for (int i = 0; i < sa.length; i++) {
								ba[i] = StringUtils.isEmpty(sa[i]) ? Boolean.FALSE : Boolean.valueOf(sa[i]);
							}
							value = ba;
						} else {
							value = new Boolean[0];
						}
						break;
					}
					default:
						throw new Exception("参数[" + p.getCode() + "].paramType[" + paramType + "]不是有效的参数数据类型");
					}
					result.put(key, value);
					result.put(__key__, __raw__);
				} catch (Exception e) {
					throw new Exception("参数[" + p.getCode() + "]类型转换错误", e);
				}
			}
		}

		for (String sp : Const.SYS_PARAMS) {
			result.put(sp, paramValues.get(sp));
		}

		if (!paramValues.containsKey("PAGE_INDEX")) {
			result.put("PAGE_INDEX", 0);
		} else {
			String pageIndex = paramValues.get("PAGE_INDEX");
			try {
				result.put("PAGE_INDEX", Integer.parseInt(pageIndex));
			} catch (Exception e) {
				throw new Exception("参数[PAGE_INDEX]类型转换错误", e);
			}
		}
		if (!paramValues.containsKey("PAGE_COUNT")) {
			result.put("PAGE_COUNT", 1);
		} else {
			String pageCount = paramValues.get("PAGE_COUNT");
			try {
				result.put("PAGE_COUNT", Integer.parseInt(pageCount));
			} catch (Exception e) {
				throw new Exception("参数[PAGE_COUNT]类型转换错误", e);
			}
		}
		return result;
	}

	public static boolean executeMetaBefore(String from, Context context, String script, Map<String, String> consts, Map<String, String> params, Map<String, Serializable> ps) throws Exception {
		if (StringUtils.isNotEmpty(script)) {
			Map<String, Object> vars = new HashMap<String, Object>();
			vars.put("CONSTS", consts);
			vars.put("raw", params);
			vars.put("params", ps);
			vars.put("context", context);
			vars.put("db", new ScriptUtil.Db(context));
			vars.put("auth", new ScriptUtil.Auth(params.get(Const.SYS_PARAMS_USER_ID)));
			vars.put("util", new ScriptUtil.Util(ps));
			vars.put("cache", new ScriptUtil.Cache((String) ps.get(Const.SYS_PARAMS_REPORT_CODE)));
			boolean result = false;
			try {
				result = ScriptUtil.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + "before", script, vars, Boolean.class, false);
				return result;
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].meta.before运行失败", e);
			}
		} else {
			return true;
		}
	}

	public static Map<String, DataSetData> executeMetaDatas(String from, Context context, Set<DataSet> datasets, Map<String, String> consts, Map<String, Serializable> ps, boolean includeVirtualDataset)
			throws Exception {
		Map<String, DataSetData> datas = new HashMap<String, DataSetData>();
		Map<String, Object> vars = new HashMap<String, Object>();
		vars.put("CONSTS", consts);
		vars.putAll(ps);
		Set<DataSet> virtualDatasets = new HashSet<DataSet>();
		for (DataSet dataset : datasets) {
			if (dataset instanceof VirtualDataSetCell) {
				if (includeVirtualDataset) {
					virtualDatasets.add(dataset);
				}
				continue;
			}
			try {
				String exec = RenderUtil.renderString(dataset.getExec(), vars);
				if (!Boolean.valueOf(exec)) {
					continue;
				}
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].exec属性的值：" + dataset.getExec(), e);
			}
			DataSet ds = null;
			if (dataset instanceof SqlDataSetCell) {
				String db = ((SqlDataSetCell) dataset).getDb();
				try {
					String _ = RenderUtil.renderString(db, vars);
					ds = ((SqlDataSetCell) dataset).create();
					((SqlDataSetCell) ds).setDb(_);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].db属性的值：" + db, e);
				}
			} else if (dataset instanceof HeterogeneousSqlDataSetCell) {
				String db = ((HeterogeneousSqlDataSetCell) dataset).getDb();
				try {
					String _ = RenderUtil.renderString(db, vars);
					ds = ((HeterogeneousSqlDataSetCell) dataset).create();
					((HeterogeneousSqlDataSetCell) ds).setDb(_);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].db属性的值：" + db, e);
				}
			} else {
				ds = dataset;
			}
			try {
				DataSetData data = ds.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + ds.getCode(), context, ps, consts);
				// script数据集可以通过context添加多个data
				if ((ds instanceof ScriptDataSetCell) && context.containsKey(Context.CONTEXT_KEY_DATASET_DATAS)) {
					@SuppressWarnings("unchecked")
					Map<CharSequence, DataSetData> contextDatas = (Map<CharSequence, DataSetData>) context.remove(Context.CONTEXT_KEY_DATASET_DATAS);
					if (contextDatas != null && !contextDatas.isEmpty()) {
						Set<CharSequence> _ = contextDatas.keySet();
						for (CharSequence key : _) {
							DataSetData value = contextDatas.get(key);
							if (value != null) {
								datas.put(ds.getCode() + "_" + key, value);
							}
						}
					}
				}
				if (data != null) {
					datas.put(ds.getCode(), data);
				}
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].dataset[" + ds.getCode() + "]执行错误", e);
			}
		}
		if (includeVirtualDataset) {
			Map<String, DataSetData> virtualDatas = new HashMap<String, DataSetData>();
			Map<String, Object> notVirtualDatas = new HashMap<String, Object>();
			notVirtualDatas.putAll(datas);
			notVirtualDatas.putAll(ps);
			for (DataSet ds : virtualDatasets) {
				try {
					String exec = RenderUtil.renderString(ds.getExec(), vars);
					if (!Boolean.valueOf(exec)) {
						continue;
					}
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "].dataset[" + ds.getCode() + "].exec属性的值：" + ds.getExec(), e);
				}
				try {
					String count = ((VirtualDataSetCell) ds).getCount();
					count = RenderUtil.renderString(count, notVirtualDatas);
					((VirtualDataSetCell) ds).setLength(Integer.valueOf(count));
					virtualDatas.put(ds.getCode(), ds.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + ds.getCode(), context, ps, consts));
				} catch (Exception e) {
					throw new Exception("sheet[" + from + "].dataset[" + ds.getCode() + "]执行错误", e);
				}
			}
			datas.putAll(virtualDatas);
			virtualDatas.clear();
			notVirtualDatas.clear();
			virtualDatasets.clear();
		}
		return datas;
	}

	public static boolean executeMetaAfter(String from, Context context, String script, Map<String, String> consts, Map<String, String> params, Map<String, Serializable> ps,
			Map<String, DataSetData> datas) throws Exception {
		if (StringUtils.isNotEmpty(script)) {
			Map<String, Object> vars = new HashMap<String, Object>();
			vars.put("CONSTS", consts);
			vars.put("raw", params);
			vars.put("params", ps);
			vars.put("datas", datas);
			vars.put("context", context);
			vars.put("db", new ScriptUtil.Db(context));
			vars.put("auth", new ScriptUtil.Auth(params.get(Const.SYS_PARAMS_USER_ID)));
			vars.put("util", new ScriptUtil.Util(ps));
			vars.put("cache", new ScriptUtil.Cache((String) ps.get(Const.SYS_PARAMS_REPORT_CODE)));
			boolean result = false;
			try {
				result = ScriptUtil.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + "after", script, vars, Boolean.class, false);
				return result;
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].meta.after运行失败", e);
			}
		} else {
			return true;
		}
	}

	public static PageCell initPage(String from, Context context, PageCell page, Map<String, String> consts, Map<String, Serializable> ps, Map<String, DataSetData> datas, boolean calcPageCount)
			throws Exception {
		page = page.create();
		page.setFrom(from);
		Map<String, Object> vars = new HashMap<String, Object>();
		vars.putAll(datas);
		vars.putAll(ps);
		vars.put("CONSTS", consts);
		try {
			String name = RenderUtil.renderString(page.getName(), vars);
			for (String _ : Const.TEMPLATE_SHEET_NAMES) {
				if (_.equalsIgnoreCase(name)) {
					throw new Exception("sheet[" + from + "].page.name的值[" + name + "]不能为模板内置sheet名");
				}
			}
			page.setName(name);
		} catch (Exception e) {
			throw new Exception("sheet[" + from + "].page.name的值计算错误", e);
		}
		String pageExp = page.getPage();
		if (!calcPageCount || pageExp == null || StringUtils.isEmpty(pageExp.trim())) {
			page.setPageCount(1);
		} else {
			try {
				pageExp = RenderUtil.renderString(pageExp, vars);
				int pageCount = Integer.parseInt(pageExp);
				if (pageCount <= 0) {
					throw new Exception("sheet[" + from + "].page.page[" + pageCount + "]的值无效");
				}
				page.setPageCount(pageCount);
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].page.page的值计算错误", e);
			}
		}
		if (calcPageCount) {
			ps.put("PAGE_COUNT", page.getPageCount());
		}
		
		String pageTotalExp = page.getTotal();
		if (!calcPageCount || pageTotalExp == null || StringUtils.isEmpty(pageTotalExp.trim())) {
			page.setPageTotal(1);
		} else {
			try {
				pageTotalExp = RenderUtil.renderString(pageTotalExp, vars);
				int pageTotal = Integer.parseInt(pageTotalExp);
				if (pageTotal <= 0) {
					throw new Exception("sheet[" + from + "].page.pageTotal[" + pageTotal + "]的值无效");
				}
				page.setPageTotal(pageTotal);
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].page.pageTotal的值计算错误", e);
			}
		}
		if (calcPageCount) {
			ps.put("PAGE_TOTAL", page.getPageTotal());
		}
		
		
		String titleExp = page.getTitle();
		if (titleExp == null || StringUtils.isEmpty(titleExp.trim())) {
			page.setTitleCount(0);
		} else {
			try {
				titleExp = RenderUtil.renderString(titleExp, vars);
				int titleCount = Integer.parseInt(titleExp);
				if (titleCount < 0) {
					throw new Exception("sheet[" + from + "].page.title[" + titleCount + "]的值无效");
				}
				page.setTitleCount(titleCount);
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].page.title的值计算错误", e);
			}
		}

		String headerExp = page.getHeader();
		if (headerExp == null || StringUtils.isEmpty(headerExp.trim())) {
			page.setHeaderCount(0);
		} else {
			try {
				headerExp = RenderUtil.renderString(headerExp, vars);
				int headerCount = Integer.parseInt(headerExp);
				if (headerCount < 0) {
					throw new Exception("sheet[" + from + "].page.header[" + headerCount + "]的值无效");
				}
				page.setHeaderCount(headerCount);
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].page.header的值计算错误", e);
			}
		}

		String footerExp = page.getFooter();
		if (footerExp == null || StringUtils.isEmpty(footerExp.trim())) {
			page.setFooterCount(0);
		} else {
			try {
				footerExp = RenderUtil.renderString(footerExp, vars);
				int footerCount = Integer.parseInt(footerExp);
				if (footerCount < 0) {
					throw new Exception("sheet[" + from + "].page.footer[" + footerCount + "]的值无效");
				}
				page.setFooterCount(footerCount);
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].page.footer的值计算错误", e);
			}
		}
		return page;
	}

	public static Set<VisibleCell> initCells(Context context, Set<VisibleCell> cells, PageCell page, Map<String, String> consts, Map<String, Serializable> ps, Map<String, DataSetData> datas)
			throws Exception {
		Set<VisibleCell> result = new LinkedHashSet<VisibleCell>();
		if (page.isDynamicVisible()) {
			try {
				result = initVisible(page, cells, ps, consts, datas);
			} catch (Exception e) {
				throw new Exception("sheet[" + page.getFrom() + "]的行列可见属性计算错误", e);
			}
		} else {
			for (VisibleCell cell : cells) {
				result.add(cell.create());
			}
		}
		return result;
	}

	public static Set<VisibleCell> initVisible(PageCell page, Set<VisibleCell> cells, Map<String, Serializable> params, Map<String, String> consts, Map<String, DataSetData> datas) throws Exception {
		Map<String, Object> vars = new HashMap<String, Object>();
		// rows
		List<RowCell> rows = page.getRows();
		for (RowCell row : rows) {
			vars.clear();
			String exp = row.getVisible();
			if (StringUtils.isEmpty(exp)) {
				row.setRowVisible(true);
			} else {
				vars.putAll(datas);
				vars.putAll(params);
				vars.put("CONSTS", consts);
				vars.put("ROW", row.getIndex() + 1);
				try {
					String value = RenderUtil.renderString(exp, vars);
					row.setRowVisible("true".equalsIgnoreCase(value));
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + page.getFrom() + "].row[" + (row.getIndex() + 1) + "].visible的公式：" + exp, e);
				}
			}
		}
		// cols
		List<ColCell> cols = page.getCols();
		for (ColCell col : cols) {
			vars.clear();
			String exp = col.getVisible();
			if (StringUtils.isEmpty(exp)) {
				col.setColVisible(true);
			} else {
				vars.putAll(datas);
				vars.putAll(params);
				vars.put("CONSTS", consts);
				vars.put("COL", col.getIndex() + 1);
				try {
					String value = RenderUtil.renderString(exp, vars);
					col.setColVisible("true".equalsIgnoreCase(value));
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + page.getFrom() + "].col[" + NumberUtil.to26(col.getIndex() + 1) + "].visible的公式：" + exp, e);
				}
			}
		}
		// cells
		Set<VisibleCell> result = new LinkedHashSet<VisibleCell>();
		for (VisibleCell cell : cells) {
			boolean rowVisible = page.getRow(cell.getRbegin()).isRowVisible();
			boolean colVisible = page.getCol(cell.getCbegin()).isColVisible();
			boolean visible = rowVisible && colVisible;
			if (visible) {
				VisibleCell _ = cell.create();
				// row
				int rbegin = cell.getRbegin();
				int rend = cell.getRend();
				for (int r = 0; r < cell.getRbegin(); r++) {
					if (!page.getRow(r).isRowVisible()) {
						rbegin--;
						rend--;
					}
				}
				for (int r = cell.getRbegin(); r <= cell.getRend(); r++) {
					if (!page.getRow(r).isRowVisible()) {
						rend--;
					}
				}
				_.setTop(rbegin);
				_.setRbegin(rbegin);
				_.setRend(rend);
				// col
				int cbegin = cell.getCbegin();
				int cend = cell.getCend();
				for (int c = 0; c < cell.getCbegin(); c++) {
					if (!page.getCol(c).isColVisible()) {
						cbegin--;
						cend--;
					}
				}
				for (int c = cell.getCbegin(); c <= cell.getCend(); c++) {
					if (!page.getCol(c).isColVisible()) {
						cend--;
					}
				}
				_.setLeft(cbegin);
				_.setCbegin(cbegin);
				_.setCend(cend);
				result.add(_);
			}
		}
		return result;
	}

	public static void initFloating(PageCell page, Map<String, String> consts, Map<String, Serializable> ps, Map<String, DataSetData> datas) throws Exception {
		Map<String, Object> vars = new HashMap<String, Object>();
		Map<String, Integer> pos = new HashMap<String, Integer>();
		vars.putAll(datas);
		vars.putAll(ps);
		vars.put("CONSTS", consts);
		Set<FloatingCell> floatings = page.getFloatingCells();
		Set<FloatingCell> _ = new LinkedHashSet<FloatingCell>();
		for (FloatingCell cell : floatings) {
			if (page.isDynamicVisible()) {
				try {
					String visible = (StringUtils.isEmpty(cell.getVisible())) ? "true" : RenderUtil.renderString(cell.getVisible(), vars);
					if (Boolean.valueOf(visible)) {
						_.add(cell);
					} else {
						continue;
					}
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].visible的公式：" + cell.getVisible(), e);
				}
			} else {
				_.add(cell);
			}
			try {
				String left = RenderUtil.renderString(cell.getPosition().get("left"), vars);
				pos.put("left", Integer.parseInt(left));
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].left的公式：" + cell.getPosition().get("left"), e);
			}
			try {
				String right = RenderUtil.renderString(cell.getPosition().get("right"), vars);
				pos.put("right", Integer.parseInt(right));
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].right的公式：" + cell.getPosition().get("right"), e);
			}
			try {
				String top = RenderUtil.renderString(cell.getPosition().get("top"), vars);
				pos.put("top", Integer.parseInt(top));
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].top的公式：" + cell.getPosition().get("top"), e);
			}
			try {
				String bottom = RenderUtil.renderString(cell.getPosition().get("bottom"), vars);
				pos.put("bottom", Integer.parseInt(bottom));
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].bottom的公式：" + cell.getPosition().get("bottom"), e);
			}

			cell.getPos().putAll(pos);
		}
		floatings.clear();
		floatings.addAll(_);
	}

	public static Block executeMainCell(PageCell page, VisibleCell cell, Map<String, DataSetData> datas) throws Exception {
		Block block = null;
		try {
			switch (cell.getType()) {
			case Const.REPORT_CELL_TYPE_SIMPLE: {
				block = Transmission.executeSimpleCell((SimpleCell) cell);
				break;
			}
			case Const.REPORT_CELL_TYPE_STATIC: {
				StaticCell sc = (StaticCell) cell;
				String dataset = sc.getDataset();
				DataSetData data = datas.get(dataset);
				if (data == null) {
					throw new Exception("未找到sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]对应的dataset[" + dataset + "]");
				}
				block = Transmission.executeStaticCell(sc, data);
				break;
			}
			case Const.REPORT_CELL_TYPE_DATA: {
				DataCell dc = (DataCell) cell;
				String dataset = dc.getDataset();
				DataSetData data = datas.get(dataset);
				if (data == null) {
					throw new Exception("未找到sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]对应的dataset[" + dataset + "]");
				}
				block = Transmission.executeDataCell(dc, data);
				break;
			}
			case Const.REPORT_CELL_TYPE_EXP: {
				ExpressionCell ec = (ExpressionCell) cell;
				String dataset = ec.getDataset();
				DataSetData data = datas.get(dataset);
				if (data == null) {
					throw new Exception("未找到sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]对应的dataset[" + dataset + "]");
				}
				block = Transmission.executeExpressionCell(ec, data);
				break;
			}
			case Const.REPORT_CELL_TYPE_CHART: {
				ChartCell cc = (ChartCell) cell;
				String dataset = cc.getDataset();
				DataSetData data = datas.get(dataset);
				if (data == null) {
					throw new Exception("未找到sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]对应的dataset[" + dataset + "]");
				}
				block = Transmission.executeChartCell(cc, data);
				break;
			}
			case Const.REPORT_CELL_TYPE_FIELD: {
				final String field = "field";
				FieldCell fc = (FieldCell) cell;
				String dataset = fc.getDataset();
				DataSetData data = datas.get(dataset);
				if (data == null) {
					throw new Exception("未找到sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]对应的dataset[" + dataset + "]");
				}
				fc.setField(field);
				block = Transmission.executeFieldCell(fc, DataSetData.toFieldData(data, field));
				break;
			}
			default: {
				break;
			}
			}
			return block;
		} catch (Exception e) {
			throw new Exception("sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]计算错误", e);
		}
	}

	public static Block executeFormCell(PageCell page, VisibleCell cell, Map<String, DataSetData> datas) throws Exception {
		Block block = null;
		try {
			switch (cell.getType()) {
			case Const.REPORT_CELL_TYPE_SIMPLE: {
				block = Transmission.executeSimpleCell((SimpleCell) cell);
				break;
			}
			case Const.REPORT_CELL_TYPE_FORM: {
				block = Transmission.executeFormCell((FormCell) cell);
				break;
			}
			default: {
				break;
			}
			}
			return block;
		} catch (Exception e) {
			throw new Exception("sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]计算错误", e);
		}
	}

	public static void initForceMerge(PageCell page, Report report, Map<String, Serializable> ps, Map<String, String> consts, Map<String, DataSetData> datas) throws Exception {
		String to = page.getName();
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);
		Map<String, Object> vars = new HashMap<String, Object>();
		vars.putAll(datas);
		vars.putAll(ps);
		vars.put("CONSTS", consts);
		Set<ResultCell> cells = new HashSet<ResultCell>();
		for (int r = 0; r < rowCount; r++) {
			for (int c = 0; c < colCount; c++) {
				ResultCell rc = report.getCell(to, r, c);
				if (rc != null) {
					if (cells.contains(rc)) {
						continue;
					}
					cells.add(rc);
					String forceMerge = rc.getForceMerge();
					if (forceMerge == null || StringUtils.isEmpty(forceMerge.trim())) {
						continue;
					}
					vars.put("COL", rc.getCbegin() + 1);
					vars.put("ROW", rc.getRbegin() + 1);
					vars.put("DATA", rc.getRecord());
					vars.put("VALUE", rc.getValue());
					try {
						forceMerge = RenderUtil.renderString(forceMerge.trim(), vars);
						rc.setForceMerge(forceMerge);
					} catch (Exception e) {
						throw new Exception("无法计算sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(rc.getRfrom(), rc.getCfrom()) + "].forceMerge的值", e);
					}
				}
			}
		}
	}

	public static Workbook generateMiddle(Report report) throws Exception {
		Set<String> froms = report.getFromSheetNames();
		Map<String, DataSetData> datas = report.getDatas();
		Workbook excel = report.getExcel(false);
		Map<String, Serializable> params = report.getParams();
		Map<String, String> consts = report.getConsts();
		for (String from : froms) {
			String to = report.getSheetMapping().get(from);
			PageCell page = report.getPage(to);
			Sheet fromsheet = excel.getSheet(from);
			FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
			Sheet tosheet = excel.createSheet(report.getPage(to).getName());
			int rowCount = report.getRowCount(to);
			int colCount = report.getColCount(to);
			boolean[] autoSizeCols = new boolean[colCount];
			boolean[] autoSizeRows = new boolean[rowCount];
			for (int r = 0; r < rowCount; r++) {
				Row row = tosheet.createRow(r);
				for (int c = 0; c < colCount; c++) {
					ResultCell rc = report.getCell(to, r, c);
					if (rc != null) {
						if (rc.isColAutoSize(c, page)) {
							autoSizeCols[c] = true;
						}
						if (rc.isRowAutoSize(r, page)) {
							autoSizeRows[r] = true;
						}
					}
					Cell cell = row.createCell(c);
					try {
						Transmission.setExcelCellValue(cell, rc, params, consts, datas);
					} catch (Exception e) {
						throw new Exception("无法根据规则生成sheet[" + from + "].cell[" + NumberUtil.toExcelPoint(rc.getRfrom(), rc.getCfrom()) + "]的值", e);
					}
					if (rc != null) {
						int h = rc.getH(r, page);
						row.setHeight((short) h);
						if (h == 0) {
							row.setZeroHeight(true);
						}
						int w = rc.getW(c, page);
						tosheet.setColumnWidth(c, rc.getW(c, page));
						if (w == 0) {
							tosheet.setColumnHidden(c, true);
						}
					}
				}
			}
			if (page.isDynamicStyle()) {
				for (int r = 0; r < rowCount; r++) {
					Row row = tosheet.getRow(r);
					for (int c = 0; c < colCount; c++) {
						ResultCell rc = report.getCell(to, r, c);
						Cell cell = row.getCell(c);
						if (rc != null) {
							try {
								CellStyle excelStyle = Transmission.getDynamicExcelCellStyle(excel, evaluator, fromsheet, cell, rc, params, consts, datas);
								if (excelStyle != null) {
									cell.setCellStyle(excelStyle);
								}
							} catch (Exception e) {
								throw new Exception("无法计算sheet[" + from + "].cell[" + NumberUtil.toExcelPoint(rc.getRfrom(), rc.getCfrom()) + "].style的值", e);
							}
						}
					}
				}
			} else {
				for (int r = 0; r < rowCount; r++) {
					Row row = tosheet.getRow(r);
					for (int c = 0; c < colCount; c++) {
						ResultCell rc = report.getCell(to, r, c);
						Cell cell = row.getCell(c);
						if (rc != null) {
							CellStyle excelStyle = Transmission.getExcelCellStyle(fromsheet, rc.getStyleRfrom(), rc.getStyleCfrom());
							if (excelStyle != null) {
								cell.setCellStyle(excelStyle);
							}
						}
					}
				}
			}
			Set<ResultCell> regions = report.getRanges(to);
			for (ResultCell rc : regions) {
				//Todo此处是为了避免合并单元格冲突  By wjq
				if(validateMergeCell(tosheet,rc)) {
					tosheet.addMergedRegion(new CellRangeAddress(rc.getRbegin(), rc.getRend(), rc.getCbegin(), rc.getCend()));
				}
			}
			regions.clear();
			for (int i = 0; i < autoSizeRows.length; i++) {
				if (autoSizeRows[i]) {
					tosheet.getRow(i).setHeight((short) -1);
				}
			}
			for (int i = 0; i < autoSizeCols.length; i++) {
				if (autoSizeCols[i]) {
					int colWidth = tosheet.getColumnWidth(i);
					double width = SheetUtil2.getColumnWidth(page.getFontWidth(), tosheet, i, true, evaluator);
					if (width != -1) {
						width *= 256;
						int maxColumnWidth = 255 * 256; // The maximum column
														// width for an
														// individual cell is
														// 255 characters
						if (width > maxColumnWidth) {
							width = maxColumnWidth;
						}
						if (width > colWidth) {
							tosheet.setColumnWidth(i, (int) (width));
							if(tosheet instanceof XSSFSheet){
								((XSSFSheet)tosheet).getColumnHelper().setColBestFit(i, true);
							}
						}
					}
				}
			}
			if(page.isDynamicZoom()){
				tosheet.setFitToPage(true);
			}
			tosheet.getPrintSetup().setLandscape(fromsheet.getPrintSetup().getLandscape());
			tosheet.getPrintSetup().setPaperSize(fromsheet.getPrintSetup().getPaperSize());
			tosheet.setMargin(XSSFSheet.TopMargin, fromsheet.getMargin(XSSFSheet.TopMargin));
			tosheet.setMargin(XSSFSheet.BottomMargin, fromsheet.getMargin(XSSFSheet.BottomMargin));
			tosheet.setMargin(XSSFSheet.LeftMargin, fromsheet.getMargin(XSSFSheet.LeftMargin));
			tosheet.setMargin(XSSFSheet.RightMargin, fromsheet.getMargin(XSSFSheet.RightMargin));
			tosheet.setMargin(XSSFSheet.HeaderMargin, fromsheet.getMargin(XSSFSheet.HeaderMargin));
			tosheet.setMargin(XSSFSheet.FooterMargin, fromsheet.getMargin(XSSFSheet.FooterMargin));
			if (page.isIncludePHF()) {
				Map<String, Object> vars = new HashMap<String, Object>();
				vars.putAll(report.getDatas());
				vars.putAll(report.getParams());
				vars.put("CONSTS", report.getConsts());
				String hleft = fromsheet.getHeader().getLeft();
				try {
					if (StringUtils.isNotEmpty(hleft)) {
						hleft = RenderUtil.renderString(hleft, vars);
					}
					tosheet.getHeader().setLeft(hleft);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]左侧页眉：" + hleft, e);
				}
				String hcenter = fromsheet.getHeader().getCenter();
				try {
					if (StringUtils.isNotEmpty(hcenter)) {
						hcenter = RenderUtil.renderString(hcenter, vars);
					}
					tosheet.getHeader().setCenter(hcenter);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]中间页眉：" + hcenter, e);
				}
				String hright = fromsheet.getHeader().getRight();
				try {
					if (StringUtils.isNotEmpty(hright)) {
						hleft = RenderUtil.renderString(hright, vars);
					}
					tosheet.getHeader().setRight(hright);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]右侧页眉：" + hright, e);
				}
				String fleft = fromsheet.getFooter().getLeft();
				try {
					if (StringUtils.isNotEmpty(fleft)) {
						fleft = RenderUtil.renderString(fleft, vars);
					}
					tosheet.getFooter().setLeft(fleft);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]左侧页脚：" + fleft, e);
				}
				String fcenter = fromsheet.getFooter().getCenter();
				try {
					if (StringUtils.isNotEmpty(fcenter)) {
						fcenter = RenderUtil.renderString(fcenter, vars);
					}
					tosheet.getFooter().setCenter(fcenter);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]中间页脚：" + fcenter, e);
				}
				String fright = fromsheet.getFooter().getRight();
				try {
					if (StringUtils.isNotEmpty(fright)) {
						fright = RenderUtil.renderString(fright, vars);
					}
					tosheet.getFooter().setRight(fright);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "]右侧页脚：" + fright, e);
				}
			}
			if (page.getTitleCount() + page.getHeaderCount() > 0) {
				tosheet.createFreezePane(0, page.getTitleCount() + page.getHeaderCount());
			}
			tosheet.setDisplayGridlines(false);
		} // end for sheets
		report.updateMiddle(excel);
		return excel;
	}

	public static void generateImage(PageCell page, Report report, Workbook excel) throws Exception {
		// TODO:
		// 这里又遍历一次全部cell，可以execute期间就把report.images初始化好，这里直接生成图片，不用再循环判断cell类型
		String to = page.getName();
		Map<String, DataSetData> datas = report.getDatas();
		Map<String, Serializable> params = report.getParams();
		Map<String, String> consts = report.getConsts();
		Map<ResultCell, byte[]> images = report.getImages(to);
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);
		for (int r = 0; r < rowCount; r++) {
			for (int c = 0; c < colCount; c++) {
				ResultCell rc = report.getCell(to, r, c);
				if ((rc != null) && (rc instanceof ChartResultCell) && (!images.containsKey(rc))) {
					ChartResultCell chart = (ChartResultCell) rc;
					byte[] png;
					try {
						png = ChartUtil.generatePNG(chart, Transmission.getPixelWidth(chart, excel.getSheet(to), report.getPage(to).getFontWidth(), 1.0f),
								Transmission.getPixelHeight(chart, excel.getSheet(to), 1.0f), params, consts, datas);
					} catch (Exception e) {
						throw new Exception("sheet[" + page.getFrom() + "].cell[" + NumberUtil.toExcelPoint(rc.getRfrom(), rc.getCfrom()) + "]生成图形失败", e);
					}
					if (png != null && png.length > 0) {
						images.put(rc, png);
					}
				}
			}
		}
	}

	public static void generateFloating(PageCell page, Report report, Workbook excel) throws Exception {
		String to = page.getName();
		Map<String, Serializable> params = report.getParams();
		Map<String, String> consts = report.getConsts();
		Map<ResultCell, byte[]> images = report.getImages(to);
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);
		int z_index = 1;
		Set<FloatingCell> floatings = report.getPage(to).getFloatingCells();
		for (FloatingCell cell : floatings) {
			int rbegin = cell.getTop();
			int rend = cell.getBottom();
			int cbegin = cell.getLeft();
			int cend = cell.getRight();
			if (rbegin < 0 || rend >= rowCount || cbegin < 0 || cend >= colCount) {
				throw new Exception("sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].position超出了报表整体范围");
			}
			if (rbegin > rend || cbegin > cend) {
				throw new Exception("sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "].position的值无效，top>bottom或者left>right");
			}
			FloatingResultCell rc = new FloatingResultCell(cell, z_index);
			byte[] png;
			try {
				png = ChartUtil.generateFloatingPNG(rc, Transmission.getPixelWidth(rc, excel.getSheet(to), report.getPage(to).getFontWidth(), 1.0f),
						Transmission.getPixelHeight(rc, excel.getSheet(to), 1.0f), params, consts);
			} catch (Exception e) {
				throw new Exception("sheet[" + page.getFrom() + "].floating[" + cell.getFrom() + "]生成图形失败", e);
			}
			if (png != null && png.length > 0) {
				images.put(rc, png);
			}
			z_index++;
		}
	}

	public static Map<String, DataSetData> executeSingleMetaData(String from, String code, Context context, Set<DataSet> datasets, Map<String, String> consts, Map<String, Serializable> ps)
			throws Exception {
		Map<String, DataSetData> datas = new HashMap<String, DataSetData>();
		Map<String, Object> vars = new HashMap<String, Object>();
		vars.put("CONSTS", consts);
		vars.putAll(ps);
		boolean isFindDataSet = false;
		for (DataSet dataset : datasets) {
			if (!dataset.getCode().equals(code)) {
				continue;
			}
			isFindDataSet = true;
			try {
				String exec = RenderUtil.renderString(dataset.getExec(), vars);
				if (!Boolean.valueOf(exec)) {
					continue;
				}
			} catch (Exception e) {
				throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].exec属性的值：" + dataset.getExec(), e);
			}
			DataSet ds = null;
			if (dataset instanceof SqlDataSetCell) {
				String db = ((SqlDataSetCell) dataset).getDb();
				try {
					String _ = RenderUtil.renderString(db, vars);
					ds = ((SqlDataSetCell) dataset).create();
					((SqlDataSetCell) ds).setDb(_);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].db属性的值：" + db, e);
				}
			} else if (dataset instanceof HeterogeneousSqlDataSetCell) {
				String db = ((HeterogeneousSqlDataSetCell) dataset).getDb();
				try {
					String _ = RenderUtil.renderString(db, vars);
					ds = ((HeterogeneousSqlDataSetCell) dataset).create();
					((HeterogeneousSqlDataSetCell) ds).setDb(_);
				} catch (Exception e) {
					throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].db属性的值：" + db, e);
				}
			} else {
				ds = dataset;
			}
			try {
				DataSetData data = ds.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + ds.getCode(), context, ps, consts);
				// script数据集可以通过context添加多个data
				if ((ds instanceof ScriptDataSetCell) && context.containsKey(Context.CONTEXT_KEY_DATASET_DATAS)) {
					@SuppressWarnings("unchecked")
					Map<CharSequence, DataSetData> contextDatas = (Map<CharSequence, DataSetData>) context.remove(Context.CONTEXT_KEY_DATASET_DATAS);
					if (contextDatas != null && !contextDatas.isEmpty()) {
						Set<CharSequence> _ = contextDatas.keySet();
						for (CharSequence key : _) {
							DataSetData value = contextDatas.get(key);
							if (value != null) {
								datas.put(ds.getCode() + "_" + key, value);
							}
						}
					}
				}
				if (data != null) {
					datas.put(ds.getCode(), data);
				}
			} catch (Exception e) {
				throw new Exception("sheet[" + from + "].dataset[" + ds.getCode() + "]执行错误", e);
			}
		}
		if (!isFindDataSet) {
			for (DataSet dataset : datasets) {
				if (code.startsWith(dataset.getCode() + "_") && (dataset instanceof ScriptDataSetCell)) {
					try {
						String exec = RenderUtil.renderString(dataset.getExec(), vars);
						if (!Boolean.valueOf(exec)) {
							continue;
						}
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + from + "].dataset[" + dataset.getCode() + "].exec属性的值：" + dataset.getExec(), e);
					}
					DataSet ds = dataset;
					try {
						DataSetData data = ds.execute(ps.get(Const.SYS_PARAMS_REPORT_CODE).toString() + from + ds.getCode(), context, ps, consts);
						// script数据集可以通过context添加多个data
						if ((ds instanceof ScriptDataSetCell) && context.containsKey(Context.CONTEXT_KEY_DATASET_DATAS)) {
							@SuppressWarnings("unchecked")
							Map<CharSequence, DataSetData> contextDatas = (Map<CharSequence, DataSetData>) context.remove(Context.CONTEXT_KEY_DATASET_DATAS);
							if (contextDatas != null && !contextDatas.isEmpty()) {
								Set<CharSequence> _ = contextDatas.keySet();
								for (CharSequence key : _) {
									DataSetData value = contextDatas.get(key);
									if (value != null) {
										datas.put(ds.getCode() + "_" + key, value);
									}
								}
							}
						}
						if (data != null) {
							datas.put(ds.getCode(), data);
						}
					} catch (Exception e) {
						throw new Exception("sheet[" + from + "].dataset[" + ds.getCode() + "]执行错误", e);
					}
				}
				if (datas.containsKey(code)) {
					break;
				}
			}
		}
		return datas;
	}

	public static String[][] toRaw(String to, Report report) throws Exception {
		if (!report.getSheetMapping().containsValue(to)) {
			throw new Exception("Report " + report.toLog() + " 不包含名为[" + to + "]的sheet");
		}
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);
		Workbook excel = report.getExcel(false);
		FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
		Sheet sheet = excel.getSheet(to);
		String[][] result = new String[rowCount][colCount];
		for (int r = 0; r < rowCount; r++) {
			for (int c = 0; c < colCount; c++) {
				ResultCell rc = report.getCell(to, r, c);
				Cell cell = sheet.getRow(r).getCell(c);
				result[r][c] = (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_DATE) ? Transmission.getExcelCellFormattedValue(cell, evaluator).replaceAll("\"", "") : Transmission
						.getExcelCellFormattedValue(cell, evaluator);
			}
		}
		return result;
	}

	public static LinkedHashMap<String, String[][]> toRaws(Report report) throws Exception {
		LinkedHashMap<String, String[][]> result = new LinkedHashMap<String, String[][]>();
		Collection<String> tos = report.getToSheetNames();
		for (String to : tos) {
			int rowCount = report.getRowCount(to);
			int colCount = report.getColCount(to);
			Workbook excel = report.getExcel(false);
			FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
			Sheet sheet = excel.getSheet(to);
			String[][] raw = new String[rowCount][colCount];
			for (int r = 0; r < rowCount; r++) {
				for (int c = 0; c < colCount; c++) {
					ResultCell rc = report.getCell(to, r, c);
					Cell cell = sheet.getRow(r).getCell(c);
					raw[r][c] = (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_DATE) ? Transmission.getExcelCellFormattedValue(cell, evaluator).replaceAll("\"", "") : Transmission
							.getExcelCellFormattedValue(cell, evaluator);
				}
			}
			result.put(to, raw);
		}
		return result;
	}
	
	public static void exportXml(Report report) throws Exception{
		
	}

	public static Workbook exportExcel(Report report) throws Exception {
		Workbook excel = report.getExcel(true);
		Collection<String> tos = report.getToSheetNames();
		FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
		boolean protectSheet = false;
		String protectPass = "";
		for (String to : tos) {
			PageCell page = report.getPage(to);
			protectSheet = page.isProtectSheet();
			protectPass = page.getProtectPass();
			Sheet fromsheet = excel.getSheet(to);
			Sheet expSheet;
			String tmpSheetName = to + ".x";
			if (!page.isIncludeFormula()) {
				expSheet = excel.createSheet(tmpSheetName);
				int row = report.getRowCount(to);
				int col = report.getColCount(to);

				boolean[] autoSizeCols = new boolean[col];
				boolean[] autoSizeRows = new boolean[row];

				for (int r = 0; r < row; r++) {
					Row exprXssfRow = expSheet.createRow(r);
					Row xssfRow = fromsheet.getRow(r);
					for (int c = 0; c < col; c++) {
						ResultCell rc = report.getCell(to, r, c);
						if (rc != null) {
							if (rc.isColAutoSize(c, page)) {
								autoSizeCols[c] = true;
							}
							if (rc.isRowAutoSize(r, page)) {
								autoSizeRows[r] = true;
							}
						}
						Cell expXssfCell = exprXssfRow.createCell(c);
						Cell xssfCell = xssfRow.getCell(c);

						Transmission.setExcelCellResultValue(expXssfCell, xssfCell, evaluator);
						CellStyle excelStyle = Transmission.getExcelCellStyle(fromsheet, r, c);
						if (excelStyle != null) {
							expXssfCell.setCellStyle(excelStyle);
						}

						if (rc != null) {
							int h = rc.getH(r, page);
							exprXssfRow.setHeight((short) h);
							if (h == 0) {
								exprXssfRow.setZeroHeight(true);
							}
							int w = rc.getW(c, page);
							expSheet.setColumnWidth(c, rc.getW(c, page));
							if (w == 0) {
								expSheet.setColumnHidden(c, true);
							}
						}
					}
				}
				Set<ResultCell> regions = report.getRanges(to);
				for (ResultCell rc : regions) {
					expSheet.addMergedRegion(new CellRangeAddress(rc.getRbegin(), rc.getRend(), rc.getCbegin(), rc.getCend()));
				}
				regions.clear();
				for (int i = 0; i < autoSizeRows.length; i++) {
					if (autoSizeRows[i]) {
						expSheet.getRow(i).setHeight((short) -1);
					}
				}
				for (int i = 0; i < autoSizeCols.length; i++) {
					if (autoSizeCols[i]) {
						int colWidth = expSheet.getColumnWidth(i);
						double width = SheetUtil2.getColumnWidth(page.getFontWidth(), expSheet, i, true, evaluator);
						if (width != -1) {
							width *= 256;
							int maxColumnWidth = 255 * 256; // The maximum
															// column width for
															// an individual
															// cell is 255
															// characters
							if (width > maxColumnWidth) {
								width = maxColumnWidth;
							}
							if (width > colWidth) {
								expSheet.setColumnWidth(i, (int) (width));
								if(expSheet instanceof XSSFSheet){
									((XSSFSheet)expSheet).getColumnHelper().setColBestFit(i, true);
								}
								
							}
						}
					}
				}
				expSheet.getPrintSetup().setLandscape(fromsheet.getPrintSetup().getLandscape());
				expSheet.getPrintSetup().setPaperSize(fromsheet.getPrintSetup().getPaperSize());
				expSheet.setMargin(XSSFSheet.TopMargin, fromsheet.getMargin(XSSFSheet.TopMargin));
				expSheet.setMargin(XSSFSheet.BottomMargin, fromsheet.getMargin(XSSFSheet.BottomMargin));
				expSheet.setMargin(XSSFSheet.LeftMargin, fromsheet.getMargin(XSSFSheet.LeftMargin));
				expSheet.setMargin(XSSFSheet.RightMargin, fromsheet.getMargin(XSSFSheet.RightMargin));
				expSheet.setMargin(XSSFSheet.HeaderMargin, fromsheet.getMargin(XSSFSheet.HeaderMargin));
				expSheet.setMargin(XSSFSheet.FooterMargin, fromsheet.getMargin(XSSFSheet.FooterMargin));
				if (page.isIncludePHF()) {
					Map<String, Object> vars = new HashMap<String, Object>();
					vars.putAll(report.getDatas());
					vars.putAll(report.getParams());
					vars.put("CONSTS", report.getConsts());
					String hleft = fromsheet.getHeader().getLeft();
					try {
						if (StringUtils.isNotEmpty(hleft)) {
							hleft = RenderUtil.renderString(hleft, vars);
						}
						expSheet.getHeader().setLeft(hleft);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]左侧页眉：" + hleft, e);
					}
					String hcenter = fromsheet.getHeader().getCenter();
					try {
						if (StringUtils.isNotEmpty(hcenter)) {
							hcenter = RenderUtil.renderString(hcenter, vars);
						}
						expSheet.getHeader().setCenter(hcenter);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]中间页眉：" + hcenter, e);
					}
					String hright = fromsheet.getHeader().getRight();
					try {
						if (StringUtils.isNotEmpty(hright)) {
							hleft = RenderUtil.renderString(hright, vars);
						}
						expSheet.getHeader().setRight(hright);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]右侧页眉：" + hright, e);
					}
					String fleft = fromsheet.getFooter().getLeft();
					try {
						if (StringUtils.isNotEmpty(fleft)) {
							fleft = RenderUtil.renderString(fleft, vars);
						}
						expSheet.getFooter().setLeft(fleft);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]左侧页脚：" + fleft, e);
					}
					String fcenter = fromsheet.getFooter().getCenter();
					try {
						if (StringUtils.isNotEmpty(fcenter)) {
							fcenter = RenderUtil.renderString(fcenter, vars);
						}
						expSheet.getFooter().setCenter(fcenter);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]中间页脚：" + fcenter, e);
					}
					String fright = fromsheet.getFooter().getRight();
					try {
						if (StringUtils.isNotEmpty(fright)) {
							fright = RenderUtil.renderString(fright, vars);
						}
						expSheet.getFooter().setRight(fright);
					} catch (Exception e) {
						throw new Exception("无法预处理sheet[" + to + "]右侧页脚：" + fright, e);
					}
				}
				if (page.getTitleCount() + page.getHeaderCount() > 0) {
					expSheet.createFreezePane(0, page.getTitleCount() + page.getHeaderCount());
				}
				expSheet.setDisplayGridlines(false);
			}

			// write chart to excel
			Drawing drawing = fromsheet.createDrawingPatriarch();
			CreationHelper helper = excel.getCreationHelper();
			Map<ResultCell, byte[]> images = report.getImages(to);
			for (ResultCell key : images.keySet()) {
				byte[] png = images.get(key);
				int id = excel.addPicture(png, XSSFWorkbook.PICTURE_TYPE_PNG);
				ClientAnchor anchor = helper.createClientAnchor();
				anchor.setCol1(key.getCbegin());
				anchor.setCol2(key.getCend() + 1);
				anchor.setRow1(key.getRbegin());
				anchor.setRow2(key.getRend() + 1);
				drawing.createPicture(anchor, id);
			}
			fromsheet.setForceFormulaRecalculation(true);

			if (!page.isIncludeFormula()) {
				int index = excel.getSheetIndex(to);
				excel.removeSheetAt(index);
				index = excel.getSheetIndex(tmpSheetName);
				excel.setSheetName(index, to);
			}
		}
		int index;
		index = excel.getSheetIndex(Const.MAIN_META);
		excel.removeSheetAt(index);
		index = excel.getSheetIndex(Const.MAIN);
		excel.removeSheetAt(index);
		index = excel.getSheetIndex(Const.FORM_META);
		excel.removeSheetAt(index);
		index = excel.getSheetIndex(Const.FORM);
		excel.removeSheetAt(index);
		index = excel.getSheetIndex(Const.STYLE);
		if (index >= 0) {
			excel.removeSheetAt(index);
		}
		index = excel.getSheetIndex(Const.STYLE_META);
		if (index >= 0) {
			excel.removeSheetAt(index);
		}
		index = excel.getSheetIndex(Const.CUSTOM_UPDATE);
		String customSheetName="";
		if (index >= 0) {
			customSheetName=excel.getSheetAt(index).getRow(0).getCell(0).getStringCellValue();
			excel.removeSheetAt(index);
		}
		for (String name : report.getSheetMapping().keySet()) {
			index = excel.getSheetIndex(name);
			if (index >= 0) {
				excel.removeSheetAt(index);
			}
		}
		if(StringUtils.isNotBlank(customSheetName)){
			excel.setSheetOrder(excel.getSheetName(excel.getNumberOfSheets()-1), 0);
			excel.setSheetName(0,customSheetName);
		}
		// excel.getCreationHelper().createFormulaEvaluator().evaluateAll();
		if(protectSheet){
			excel.getSheetAt(0).protectSheet(protectPass);
		}
		excel.setForceFormulaRecalculation(true);
		excel.setFirstVisibleTab(0);
		return excel;
	}

	public static Workbook decompile(Report report) throws Exception {
		Workbook excel = report.getExcel(true);
		Collection<String> tos = report.getToSheetNames();
		for (String to : tos) {
			excel.removeSheetAt(excel.getSheetIndex(to));
		}
		return excel;
	}

	public static Template loadTemplate(Workbook excel) throws Exception {
		// try ... finally excel.close
		try {
			//Excel公式
			FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
			Template template = new Template(Transmission.excelToBytes(excel));
			Piston.readFormMeta(template, excel, evaluator);
			Piston.readForm(template, excel, evaluator);
			Piston.readMainMeta(template, excel, evaluator);
			Piston.readMain(template, excel, evaluator);
			Piston.readStyle(template, excel, evaluator);
			Piston.readMainPicAndFormulaDataSetsOffset(template, excel);
			return template;
		} finally {
			excel.close();
		}
	}

	public static Template loadCustomTemplate(Workbook sofaExcel,Workbook customExcel) throws Exception{
		try {
			FormulaEvaluator evaluator = sofaExcel.getCreationHelper().createFormulaEvaluator();
			if(-1 == customExcel.getSheetIndex(Const.CUSTOM_UPDATE)){
				String customSheetName = customExcel.getSheetAt(0).getSheetName();
				copySheet(Const.FORM_META,Const.FORM_META,sofaExcel,customExcel,false);
				copySheet(Const.FORM,Const.FORM,sofaExcel,customExcel,true);
				copySheet(Const.MAIN_META,Const.MAIN_META,sofaExcel,customExcel,false);
				copySheet(Const.MAIN,customSheetName,sofaExcel,customExcel,false);
				int _mainSheetIndex = customExcel.getSheetIndex(Const.MAIN);
				if(_mainSheetIndex == -1){
					customExcel.setSheetName(0, Const.MAIN);
				}
				copySheet(Const.STYLE,Const.STYLE,sofaExcel,customExcel,true);
				Sheet mainupdate = customExcel.createSheet(Const.CUSTOM_UPDATE);
				mainupdate.createRow(0).createCell(0).setCellValue(customSheetName);
			}
			
//			//这个流从response获取，再配置响应头信息就可以实现下载；
//			OutputStream out = null;
//			try {
//				out = new FileOutputStream(new File("E:/download", "test.xlsx"));
//			} catch (FileNotFoundException e1) {
//				e1.printStackTrace();
//			}
//
//			try {
//				customExcel.write(out);
//
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
			
			Template template = new Template(Transmission.excelToBytes(customExcel));
			
			
			Piston.readFormMeta(template, customExcel, evaluator);
			Piston.readForm(template, customExcel, evaluator);
			Piston.readMainMeta(template, customExcel, evaluator);
			Piston.readMain(template, customExcel, evaluator);
			Piston.readStyle(template, customExcel, evaluator);
			Piston.readMainPicAndFormulaDataSetsOffset(template, customExcel);
			return template;
		} finally {
			customExcel.close();
			sofaExcel.close();
		}
	}

	private static void copySheet(String srcSheetName,String destSheetName,Workbook srcwb, Workbook destwb,boolean isCopyStyle) {
		try {
			Sheet srcSheet = srcwb.getSheet(srcSheetName);
			if (srcSheet != null) {
				Sheet destSheet = destwb.getSheet(destSheetName);  
				if (destSheet == null) {  
					destSheet = destwb.createSheet(destSheetName);  
				}  
				SheetUtils.copySheet(srcSheet, destSheet, srcwb, destwb,isCopyStyle);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public static String previewMainSheet(String to, Report report, float zoom, int offset, int limit) throws Exception {
		Workbook excel = report.getExcel(false);
		PageCell page = report.getPage(to);
		// css---------------------------------------------------------------------------------------
		StringBuilder css = Transmission.generatePreviewCss(page, report, excel.getSheet(page.getFrom()), zoom);
		// js----------------------------------------------------------------------------------------
		StringBuilder js = Transmission.generatePreviewJs(page, report);
		// table-------------------------------------------------------------------------------------
		String code = report.getParams().get("SYS_REPORT_CODE").toString();
		Sheet sheet = excel.getSheet(to);
		FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
		Set<ResultCell> visited = new HashSet<ResultCell>();
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);

		StringBuilder colgroup = new StringBuilder();
		colgroup.append("<colgroup").append(" colcount=").append("\"").append(colCount).append("\"").append(">");
		int tableWidth = 0;
		for (int col = 0; col < colCount; col++) {
			int width = Transmission.getColPixelWidth(sheet, col, page.getFontWidth(), zoom);
			tableWidth += width;
			colgroup.append("<col").append(" col=").append("\"").append(col).append("\"").append(" colswidth=").append("\"").append(tableWidth).append("\"").append(" style=").append("\"")
					.append("width:").append(width).append("px;").append("\"").append(">").append("</col>");
		}
		colgroup.append("</colgroup>");
		Map<String, Integer> _ = new HashMap<String, Integer>();
		StringBuilder tableAttrs = Transmission.generateReportPreviewAttrs(page, report, _);
		if (offset == 0 && limit == -1 && _.containsKey("page-size")) {
			limit = _.get("page-size");
		}
		StringBuilder table = new StringBuilder();
		table.append("\n");
		table.append("<table").append(" id=").append("\"").append("report-").append(code).append("\"").append(" rowcount=").append("\"").append(rowCount).append("\"").append(" colcount=")
				.append("\"").append(colCount).append("\"").append(" titlecount=").append("\"").append(page.getTitleCount()).append("\"").append(" headercount=").append("\"")
				.append(page.getHeaderCount()).append("\"").append(" detailcount=").append("\"").append(rowCount - page.getTitleCount() - page.getHeaderCount() - page.getFooterCount()).append("\"")
				.append(" footercount=").append("\"").append(page.getFooterCount()).append("\"").append(" class=").append("\"").append("report").append("\"").append(" style=").append("\"")
				.append("width:").append(tableWidth).append("px;").append("border-collapse:collapse;").append("table-layout:fixed;").append("\"").append(tableAttrs).append(">");
		table.append(colgroup.toString());
		/*
		 * table.append("<thead")
		 * .append(" rowcount=").append("\"").append(report
		 * .getPage().getTitleCount
		 * ()+report.getPage().getHeaderCount()).append("\"")
		 * .append(" colcount=").append("\"").append(colCount).append("\"")
		 * .append(">");
		 */
		// title-------------------------------------------------------------------------------------
		for (int row = 0; row < page.getTitleCount(); row++) {
			if (report.isRowAutoSize(to, row)) {
				table.append("<tr").append(" class=").append("\"").append("title").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("\"")
						.append(">");
			} else {
				table.append("<tr").append(" class=").append("\"").append("title").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("height:")
						.append(Transmission.getRowPixelHeight(sheet, row, zoom)).append("px;").append("\"").append(">");
			}
			for (int col = 0; col < colCount; col++) {
				ResultCell cell = report.getCell(to, row, col);
				int rowspan = 1;
				int colspan = 1;
				if (cell == null) {
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"")
							// .append(" data-type=").append("\"-1\"")
							// .append(" data-value=").append("\"\"")
							.append("></td>");
					continue;
				}
				if (visited.contains(cell)) {
					continue;
				}
				visited.add(cell);
				Cell excelCell = sheet.getRow(row).getCell(col);
				String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
				StringBuilder attrs = Transmission.generateCellPreviewAttrs(page, report, cell, excelCell, evaluator);
				rowspan = cell.getRowInc();
				colspan = cell.getColInc();
				table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
						.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"")
						// .append(" data-type=").append("\"").append(cell.getDataType()).append("\"")
						// .append(" data-value=").append("\"").append(getExcelCellEditorValue(excelCell)).append("\"")
						.append(attrs).append(">");
				try {
					table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, zoom));
				} catch (Exception e) {
					throw new Exception("无法将sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
				}
				table.append("</td>");
			}
			table.append("</tr>");
		}
		// header------------------------------------------------------------------------------------
		for (int row = page.getTitleCount(); row < page.getTitleCount() + page.getHeaderCount(); row++) {
			if (report.isRowAutoSize(to, row)) {
				table.append("<tr").append(" class=").append("\"").append("header").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("\"")
						.append(">");
			} else {
				table.append("<tr").append(" class=").append("\"").append("header").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("height:")
						.append(Transmission.getRowPixelHeight(sheet, row, zoom)).append("px;").append("\"").append(">");
			}
			for (int col = 0; col < colCount; col++) {
				ResultCell cell = report.getCell(to, row, col);
				int rowspan = 1;
				int colspan = 1;
				if (cell == null) {
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"")
							// .append(" data-type=").append("\"-1\"")
							// .append(" data-value=").append("\"\"")
							.append("></td>");
					continue;
				}
				if (visited.contains(cell)) {
					continue;
				}
				visited.add(cell);
				Cell excelCell = sheet.getRow(row).getCell(col);
				String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
				StringBuilder attrs = Transmission.generateCellPreviewAttrs(page, report, cell, excelCell, evaluator);
				rowspan = cell.getRowInc();
				colspan = cell.getColInc();
				table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
						.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"")
						// .append(" data-type=").append("\"").append(cell.getDataType()).append("\"")
						// .append(" data-value=").append("\"").append(getExcelCellEditorValue(excelCell)).append("\"")
						.append(attrs).append(">");
				try {
					table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, zoom));
				} catch (Exception e) {
					throw new Exception("无法将sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
				}
				table.append("</td>");
			}
			table.append("</tr>");
		}
		/*
		 * table.append("</thead>"); table.append("<tbody")
		 * .append(" rowcount=")
		 * .append("\"").append(rowCount-report.getPage().getTitleCount
		 * ()-report.getPage().getHeaderCount()).append("\"")
		 * .append(" colcount=").append("\"").append(colCount).append("\"")
		 * .append(">");
		 */
		// detail------------------------------------------------------------------------------------
		if (offset >= 0 && limit >= 0) {
			int count = 0;
			for (int row = page.getTitleCount() + page.getHeaderCount() + offset; row < rowCount - page.getFooterCount(); row++) {
				if (count >= limit)
					break;
				count++;
				if (report.isRowAutoSize(to, row)) {
					table.append("<tr").append(" class=").append("\"").append("detail").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("\"")
							.append(">");
				} else {
					table.append("<tr").append(" class=").append("\"").append("detail").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"")
							.append("height:").append(Transmission.getRowPixelHeight(sheet, row, zoom)).append("px;").append("\"").append(">");
				}
				for (int col = 0; col < colCount; col++) {
					ResultCell cell = report.getCell(to, row, col);
					int rowspan = 1;
					int colspan = 1;
					if (cell == null) {
						table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"")
								.append(rowspan).append("\"").append(" colspan=").append("\"").append(colspan).append("\"")
								// .append(" data-type=").append("\"-1\"")
								// .append(" data-value=").append("\"\"")
								.append("></td>");
						continue;
					}
					if (visited.contains(cell)) {
						continue;
					}
					visited.add(cell);
					Cell excelCell = sheet.getRow(row).getCell(col);
					String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
					StringBuilder attrs = Transmission.generateCellPreviewAttrs(page, report, cell, excelCell, evaluator);
					rowspan = cell.getRowInc();
					colspan = cell.getColInc();
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"")
							// .append(" data-type=").append("\"").append(cell.getDataType()).append("\"")
							// .append(" data-value=").append("\"").append(getExcelCellEditorValue(excelCell)).append("\"")
							.append(attrs).append(">");
					try {
						table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, zoom));
					} catch (Exception e) {
						throw new Exception("无法将sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
					}
					table.append("</td>");
				}
				table.append("</tr>");
			}
		} else {
			for (int row = page.getTitleCount() + page.getHeaderCount(); row < rowCount - page.getFooterCount(); row++) {
				if (report.isRowAutoSize(to, row)) {
					table.append("<tr").append(" class=").append("\"").append("detail").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("\"")
							.append(">");
				} else {
					table.append("<tr").append(" class=").append("\"").append("detail").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"")
							.append("height:").append(Transmission.getRowPixelHeight(sheet, row, zoom)).append("px;").append("\"").append(">");
				}
				for (int col = 0; col < colCount; col++) {
					ResultCell cell = report.getCell(to, row, col);
					int rowspan = 1;
					int colspan = 1;
					if (cell == null) {
						table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"")
								.append(rowspan).append("\"").append(" colspan=").append("\"").append(colspan).append("\"")
								// .append(" data-type=").append("\"-1\"")
								// .append(" data-value=").append("\"\"")
								.append("></td>");
						continue;
					}
					if (visited.contains(cell)) {
						continue;
					}
					visited.add(cell);
					Cell excelCell = sheet.getRow(row).getCell(col);
					String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
					StringBuilder attrs = Transmission.generateCellPreviewAttrs(page, report, cell, excelCell, evaluator);
					rowspan = cell.getRowInc();
					colspan = cell.getColInc();
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"")
							// .append(" data-type=").append("\"").append(cell.getDataType()).append("\"")
							// .append(" data-value=").append("\"").append(getExcelCellEditorValue(excelCell)).append("\"")
							.append(attrs).append(">");
					try {
						table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, zoom));
					} catch (Exception e) {
						throw new Exception("无法将sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
					}
					table.append("</td>");
				}
				table.append("</tr>");
			}
		}
		/*
		 * table.append("</tbody>"); table.append("<tfoot")
		 * .append(" rowcount=")
		 * .append("\"").append(report.getPage().getFooterCount()).append("\"")
		 * .append(" colcount=").append("\"").append(colCount).append("\"")
		 * .append(">");
		 */
		// footer------------------------------------------------------------------------------------
		for (int row = rowCount - page.getFooterCount(); row < rowCount; row++) {
			if (report.isRowAutoSize(to, row)) {
				table.append("<tr").append(" class=").append("\"").append("footer").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("\"")
						.append(">");
			} else {
				table.append("<tr").append(" class=").append("\"").append("footer").append("\"").append(" row=").append("\"").append(row).append("\"").append(" style=").append("\"").append("height:")
						.append(Transmission.getRowPixelHeight(sheet, row, zoom)).append("px;").append("\"").append(">");
			}
			for (int col = 0; col < colCount; col++) {
				ResultCell cell = report.getCell(to, row, col);
				int rowspan = 1;
				int colspan = 1;
				if (cell == null) {
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"")
							// .append(" data-type=").append("\"-1\"")
							// .append(" data-value=").append("\"\"")
							.append("></td>");
					continue;
				}
				if (visited.contains(cell)) {
					continue;
				}
				visited.add(cell);
				Cell excelCell = sheet.getRow(row).getCell(col);
				String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
				StringBuilder attrs = Transmission.generateCellPreviewAttrs(page, report, cell, excelCell, evaluator);
				rowspan = cell.getRowInc();
				colspan = cell.getColInc();
				table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
						.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"")
						// .append(" data-type=").append("\"").append(cell.getDataType()).append("\"")
						// .append(" data-value=").append("\"").append(getExcelCellEditorValue(excelCell)).append("\"")
						.append(attrs).append(">");
				try {
					table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, zoom));
				} catch (Exception e) {
					throw new Exception("无法将sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
				}
				table.append("</td>");
			}
			table.append("</tr>");
		}
		/*
		 * table.append("</tfoot>");
		 */
		table.append("</table>");
		table.append("\n");
		// floatings---------------------------------------------------------------------------------
		StringBuilder floatings = new StringBuilder();
		if (!(offset >= 0 && limit >= 0)) {
			floatings.append("\n");
			Set<ResultCell> cells = report.getImages(to).keySet();
			for (ResultCell rc : cells) {
				if (rc instanceof FloatingResultCell) {
					int top = 0;
					for (int r = 0; r < rc.getRbegin(); r++) {
						top += (Transmission.getRowPixelHeight(sheet, r, zoom));
					}
					int left = 0;
					for (int c = 0; c < rc.getCbegin(); c++) {
						left += (Transmission.getColPixelWidth(sheet, c, page.getFontWidth(), zoom));
					}
					byte[] png = ImageUtil.zoom(report.getImages(to).get(rc), zoom);
					String data = Base64.encodeBase64String(png);
					floatings.append("<img").append(" x=").append("\"").append(left).append("\"").append(" y=").append("\"").append(top).append("\"").append(" class=").append("\"")
							.append("floating-cell").append("\"").append(" style=").append("\"").append("display:block;position:absolute;").append("z-index:")
							.append(((FloatingResultCell) rc).getZindex()).append(";").append("width:").append(Transmission.getPixelWidth(rc, sheet, page.getFontWidth(), zoom)).append("px;")
							.append("height:").append(Transmission.getPixelHeight(rc, sheet, zoom)).append("px;").append("\"").append(" alt=").append("\"").append("floating").append("\"")
							.append(" src=").append("\"").append("data:image/png;base64,").append(data).append("\"").append("/>");
				}
			}
			floatings.append("\n");
		}
		return css.append(table).append(floatings).append(js).toString();
	}

	public static String previewFormSheet(String to, Report report) throws Exception {
		Workbook excel = report.getExcel(false);
		PageCell page = report.getPage(to);
		// css
		StringBuilder css = Transmission.generatePreviewCss(page, report, excel.getSheet(page.getFrom()), 1.0f);
		// js
		StringBuilder js = Transmission.generatePreviewJs(page, report);
		// table
		String code = report.getParams().get("SYS_REPORT_CODE").toString();
		Sheet sheet = excel.getSheet(to);
		FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
		Set<ResultCell> visited = new HashSet<ResultCell>();
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);

		StringBuilder colgroup = new StringBuilder();
		colgroup.append("<colgroup>");
		int tableWidth = 0;
		for (int col = 0; col < colCount; col++) {
			int width = Transmission.getColPixelWidth(sheet, col, page.getFontWidth(), 1.0f);
			tableWidth += width;
			colgroup.append("<col style=").append("\"").append("width:").append(width).append("px;").append("\"").append(">").append("</col>");
		}
		colgroup.append("</colgroup>");

		StringBuilder table = new StringBuilder();
		table.append("\n");
		table.append("<table").append(" id=").append("\"").append("report-").append(code).append("\"").append(" class=").append("\"report\"").append(" style=").append("\"").append("width:")
				.append(tableWidth).append("px;").append("border-collapse:collapse;").append("table-layout:fixed;").append("\"").append(">");
		table.append(colgroup.toString());
		for (int row = 0; row < rowCount; row++) {
			table.append("<tr style=").append("\"").append("height:").append(Transmission.getRowPixelHeight(sheet, row, 1.0f)).append("px;").append("\"").append(">");
			for (int col = 0; col < colCount; col++) {
				ResultCell cell = report.getCell(to, row, col);
				int rowspan = 1;
				int colspan = 1;
				if (cell == null) {
					table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
							.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(">");
					continue;
				}
				if (visited.contains(cell)) {
					continue;
				}
				visited.add(cell);
				String cssClass = "report-" + code + "-" + cell.getStyleRfrom() + "-" + cell.getStyleCfrom();
				rowspan = cell.getRowInc();
				colspan = cell.getColInc();
				table.append("<td").append(" row=").append("\"").append(row).append("\"").append(" col=").append("\"").append(col).append("\"").append(" rowspan=").append("\"").append(rowspan)
						.append("\"").append(" colspan=").append("\"").append(colspan).append("\"").append(" class=").append("\"").append(cssClass).append("\"").append(" style=").append("\"")
						.append("\"").append(">");
				Cell excelCell = sheet.getRow(row).getCell(col);
				try {
					table.append(Transmission.generateCellPreviewContent(page, report, cell, excelCell, evaluator, 1.0f));
				} catch (Exception e) {
					throw new Exception("无法将sheet[" + Const.FORM + "].cell[" + NumberUtil.toExcelPoint(cell.getRfrom(), cell.getCfrom()) + "]的值转换为HTML", e);
				}
				table.append("</td>");
			}
			table.append("</tr>");
		}
		table.append("</table>");
		table.append("\n");
		return css.append(table).append(js).toString();
	}
	
	/**
	 * 根据模板获取有效的验证开始行号
	 * @param report
	 * @return
	 * @throws Exception
	 */
	public static String getValidateRowNumber(Report report) throws Exception {

		Sheet sheet = report.getExcel(true).getSheet(Const.MAIN);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.MAIN + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		{
			for (int r = 0; r < rowcount; r++) {
				Row row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					Cell cell = row.getCell(c);
					String value = Transmission.getExcelCellRawValue(cell);
					try {
						int type = Transmission.getReportCellType(value);
						if (type == Const.REPORT_CELL_TYPE_DATA) {
							return String.valueOf(r);
						}

					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(r, c)+ "]的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
					}
				}
			}
		}
		return null;
	}
	
	/**
	 * 根据造成图片浮动的数据集以及数据集来复制图片
	 * @param report
	 * @return
	 * @throws Exception
	 */
	public static void writePicWithDatasetOffset(Sheet srcSheet, Sheet destSheet,ArrayList[] picOffsets,Map<String, DataSetData> datas){
		Map<XSSFClientAnchor, XSSFPictureData> sheetIndexPicMap = new HashMap<XSSFClientAnchor, XSSFPictureData>();
		Drawing srcDrawing = srcSheet.createDrawingPatriarch();
		Drawing destDrawing = destSheet.createDrawingPatriarch();
		if(srcDrawing instanceof XSSFDrawing && destDrawing instanceof XSSFDrawing){
			List<XSSFShape> shapes = ((XSSFDrawing)srcDrawing).getShapes();
			for (int i=0;i<shapes.size();i++) {
				if (shapes.get(i) instanceof XSSFPicture) {
					XSSFPicture pic = (XSSFPicture) shapes.get(i);
					XSSFClientAnchor originAnchor = (XSSFClientAnchor) pic.getAnchor();    //pic.getPreferredSize() 会造成图片缩放
					ArrayList<String> dataSetOffset = picOffsets[i];
					int rowOffset = 0;
					if (dataSetOffset != null && dataSetOffset.size() > 0) {
						for (String dataset : dataSetOffset) {
							rowOffset += datas.get(dataset).getCount();
						}
					}
					int picIndex = destSheet.getWorkbook().addPicture(pic.getPictureData().getData(), pic.getPictureData().getPictureType());
					destDrawing.createPicture(offsetAnchor(originAnchor, rowOffset), picIndex);
				}
			}
		}else if(srcDrawing instanceof HSSFPatriarch && destDrawing instanceof HSSFPatriarch){
			List<HSSFShape> shapes = ((HSSFPatriarch)srcDrawing).getChildren();
			for (int i=0;i<shapes.size();i++) {
				if (shapes.get(i) instanceof HSSFPicture) {
					HSSFPicture pic = (HSSFPicture) shapes.get(i);
					//HSSFClientAnchor originAnchor = (HSSFClientAnchor) pic.getAnchor();    //pic.getPreferredSize() 会造成图片缩放
					HSSFClientAnchor originAnchor = pic.getClientAnchor(); 
					ArrayList<String> dataSetOffset = picOffsets[i];
					int rowOffset = 0;
					if (dataSetOffset != null && dataSetOffset.size() > 0) {
						for (String dataset : dataSetOffset) {
							rowOffset += datas.get(dataset).getCount();
						}
					}
					int picIndex = destSheet.getWorkbook().addPicture(pic.getPictureData().getData(), pic.getPictureData().getPictureType());
					destDrawing.createPicture(offsetHSSFAnchor(originAnchor, rowOffset), picIndex);
				}
			}
		}

	}

	private static Template readMainPicAndFormulaDataSetsOffset(Template template, Workbook excel) throws Exception{
		Sheet srcSheet = excel.getSheet(Const.MAIN);
		if (srcSheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.MAIN + "]");
		}
		List<Integer> picRow = new ArrayList<Integer>();
		Drawing drawing = srcSheet.createDrawingPatriarch();
		if(drawing instanceof XSSFDrawing){
			List<XSSFShape> shapes = ((XSSFDrawing) drawing).getShapes();
			for (Shape shape : shapes) {
				if(shape instanceof XSSFPicture) {
					XSSFPicture pic = (XSSFPicture) shape;
					XSSFClientAnchor anchor = pic.getPreferredSize();
					picRow.add(anchor.getRow1());    //
				}
			}
		}else if(drawing instanceof HSSFPatriarch){
			List<HSSFShape> shapes =((HSSFPatriarch) drawing).getChildren();
			for (Shape shape : shapes) {
				if(shape instanceof HSSFPicture) {
					HSSFPicture pic = (HSSFPicture) shape;
					HSSFClientAnchor anchor = pic.getClientAnchor();
					picRow.add(anchor.getRow1());    //
				}
			}
		}
		ArrayList[] picOffsets =new ArrayList[picRow.size()];	
		List<Entry<String,Integer>> dataSetsRows=new ArrayList<Entry<String,Integer>>();    
		int rowCount = srcSheet.getLastRowNum();// 总行数  
		Row row = null;
		Cell cell = null;
		ObjectMapper mapper = new ObjectMapper();
		JsonNode jsonNode = null;
		for(int i=0;i<rowCount;i++){
			row = srcSheet.getRow(i);
			if(row==null){
				continue;
			}
			int colCount = row.getLastCellNum();  //总列数
			for(int j=0;j<colCount;j++){
				cell = row.getCell(j);
				if(cell!=null && cell.getCellType()==CellType.STRING.getCode()){
					String cellValue = cell.getStringCellValue();
					if(StringUtils.isNotBlank(cellValue)&&cellValue.contains("dataset") && cellValue.startsWith("#")){
						try {
							jsonNode = mapper.readTree(cellValue.substring(1));
							if(mapper.writeValueAsString(jsonNode.path("extend")).replace("\"","").equals("2")){
								String dataset = mapper.writeValueAsString(jsonNode.path("dataset")).replace("\"","");
								if(!StringUtils.isEmpty(dataset) && !"null".equals(dataset)) {
									for(int pics=0;pics<picRow.size();pics++){
										if(picRow.get(pics)>i){
											if(picOffsets[pics]==null){
												ArrayList<String> picOffset =  new ArrayList<String>();
												picOffset.add(dataset);
												picOffsets[pics]=picOffset;
											}else{
												picOffsets[pics].add(dataset);
											}
										}
									}
									//System.out.println("***************dataset="+dataset+" row="+i);
									dataSetsRows.add(new DataSetsRowsEntry(dataset,i));    //每一个dataset对应的行
									break;
								}
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		template.setMainPicDataSetsOffset(picOffsets);
		template.setDataSetsRows(dataSetsRows);
		return template;
	}
	
	
	public static XSSFClientAnchor offsetAnchor(XSSFClientAnchor anchor,int rowOffset){
		if(anchor.getRow1()+rowOffset<0){
			rowOffset=0-anchor.getRow1();
		}
		XSSFClientAnchor newanchor = new XSSFClientAnchor(anchor.getDx1(),anchor.getDy1(),anchor.getDx2(),anchor.getDy2(),anchor.getCol1(),anchor.getRow1()+rowOffset,anchor.getCol2(),anchor.getRow2()+rowOffset);
		return newanchor;
	}
	
	public static HSSFClientAnchor offsetHSSFAnchor(HSSFClientAnchor anchor,int rowOffset){
		if(anchor.getRow1()+rowOffset<0){
			rowOffset=0-anchor.getRow1();
		}
		int dx1=anchor.getDx1();
		if(anchor.getDx1()>1023){
			dx1=1023;
		}
		int dy1=anchor.getDy1();
		if(anchor.getDy1()>255){
			dy1=255;
		}
		int dx2=anchor.getDx2();
		if(anchor.getDx2()>1023){
			dx2=1023;
		}
		int dy2=anchor.getDy2();
		if(anchor.getDy2()>255){
			dy2=255;
		}
		HSSFClientAnchor newanchor = new HSSFClientAnchor(dx1,dy1,dx2,dy2,anchor.getCol1(),anchor.getRow1()+rowOffset,anchor.getCol2(),anchor.getRow2()+rowOffset);
		return newanchor;
	}
	
	/**
	 * 
	 * @param xSSFWorkbook
	 * @return
	 * @throws Exception
	 */
	public static int[] getValidateCoordinate(XSSFWorkbook excel) throws Exception {
		
		XSSFSheet sheet = excel.getSheet(Const.MAIN);
		if (sheet == null) {
			throw new Exception("模板中缺少sheet[" + Const.MAIN + "]");
		}
		Coordinate coordinate = Transmission.getTrueRowColCount(sheet);
		int rowcount = coordinate.row;
		int colcount = coordinate.col;
		{
			for (int r = 0; r < rowcount; r++) {
				XSSFRow row = sheet.getRow(r);
				for (int c = 0; c < colcount; c++) {
					XSSFCell cell = row.getCell(c);
					String value = Transmission.getExcelCellRawValue(cell);
					try {
						int type = Transmission.getReportCellType(value);
						if (type == Const.REPORT_CELL_TYPE_DATA) {
							return new int[]{r,c};
						}

					} catch (Exception e) {
						throw new Exception("无法解析sheet[" + Const.MAIN + "].cell[" + NumberUtil.toExcelPoint(r, c)+ "]的值，缺少必要的属性或不是有效的#JSON格式或单元格类型错误", e);
					}
				}
			}
		}
		return null;
	}
	
	private static boolean validateMergeCell(Sheet tosheet,ResultCell rc) {
		List<CellRangeAddress> cellRangeAddresses = tosheet.getMergedRegions();
		Boolean fl =true;
		for (final CellRangeAddress existingRegion : cellRangeAddresses) {
            if (existingRegion.intersects(new CellRangeAddress(rc.getRbegin(), rc.getRend(), rc.getCbegin(), rc.getCend()))) {
            	fl = false;
				continue;
            }
        }
		return fl;
	}
}

class DataSetsRowsEntry<K, V> implements Map.Entry<K,V> {
    private final K key;
    private V value;
    public DataSetsRowsEntry(K key, V value) {
        this.key = key;
        this.value = value;
    }
    @Override
    public K getKey() {
        return key;
    }
    @Override
    public V getValue() {
        return value;
    }
	@Override
	public V setValue(V value) {
		return null;
	}
}
